﻿using Kingdee.BOS.App.Data;
using Kingdee.BOS;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Util;
using Kingdee.BOS.Core;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Core.DynamicForm.PlugIn.Args;

namespace NS
{
    [HotUpdate]
    [Description("供应商月采购量")]
    public class MonthlyProcurementVolume : SysReportBaseService
    {
        /// 初始化事件：在此事件中，设置报表基本属性
        public override void Initialize()
        {
            base.Initialize();
            // 简单账表类型：普通、树形、分页
            this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
            this.ReportProperty.BillKeyFieldName = "序号";
            // 报表名称
            this.ReportProperty.ReportName = new LocaleValue("供应商月采购量", base.Context.UserLocale.LCID);
            this.IsCreateTempTableByPlugin = true;
            this.ReportProperty.IsUIDesignerColumns = false;
            this.ReportProperty.IsGroupSummary = true;
            this.ReportProperty.SimpleAllCols = false;
            // 单据主键：两行FID相同，则为同一单的两条分录，单据编号可以不重复显示
            this.ReportProperty.PrimaryKeyFieldName = "序号";
            this.ReportProperty.IsDefaultOnlyDspSumAndDetailData = true;
            // 报表主键字段名：默认为FIDENTITYID，可以修改
            this.ReportProperty.IdentityFieldName = "FIDENTITYID";
            // 设置精度控制
            //List list = new List();
            //// 数量
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "FQty",
            //    DecimalControlFieldName = "FUnitPrecision"
            //});
            //// 单价
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "FTAXPRICE",
            //    DecimalControlFieldName = "FPRICEDIGITS"
            //});
            //// 金额
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "FALLAMOUNT",
            //    DecimalControlFieldName = "FAMOUNTDIGITS"
            //});
            //this.ReportProperty.DecimalControlFieldList = list;
        }

        public override string GetTableName()
        {
            var result = base.GetTableName();
            return result;
        }

        public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
        {
            base.BuilderReportSqlAndTempTable(filter, tableName);
            // 拼接过滤条件 ： filter
            // 略
            // 默认排序字段：需要从filter中取用户设置的排序字段
            //string seqFld = string.Format(base.KSQL_SEQ, "ZHJ");

            DateTime FStartDate;
            DateTime FEndDate;
            //string FMaterialid = "";
            string year = "";
            DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
            StringBuilder SQLFilter = new StringBuilder();
            //StringBuilder SQLFilter1 = new StringBuilder();
            FStartDate = Convert.ToDateTime(dyFilter["FDate"]);//日期
            if(!FStartDate.ToString().Contains("0001"))
            {
                year = FStartDate.ToString("yyyy");
            }
            ////过滤 日期
            //if (!FStartDate.ToString().Contains("0001") && !FEndDate.ToString().Contains("0001"))
            //{
            //    SQLFilter.Append("and t.FDATE>='" + FStartDate + "' and t.FDATE<='" + FEndDate + "'");
            //}
            ////if (!dyFilter.IsNullOrEmpty())
            ////{
            //    if (!Convert.ToString(dyFilter["FMaterialid_id"]).Equals("0"))
            //    {
            //        SQLFilter1.Append("and t1.FMATERIALID='" + dyFilter["FMaterialid_id"] + "'");
            //    }
            //    if (!Convert.ToString(dyFilter["FSuppiler_id"]).Equals("0"))
            //    {
            //        SQLFilter1.Append("and t6.FSUPPLIERID='" + dyFilter["FSuppiler_id"] + "'");
            //    }
            //    if (!Convert.ToString(dyFilter["FOrgId_id"]).Equals("0"))
            //    {
            //        SQLFilter1.Append("and t.FPURCHASEORGID='" + dyFilter["FOrgId_id"] + "'");
            //    }
            //if (!Convert.ToString(dyFilter["FBillNo"]).Equals(""))
            //{
            //    SQLFilter1.Append("and t.FBillNo='" + dyFilter["FBillNo"] + "'");
            //}
            //}

            // 取数SQL
            string sql = string.Format(@"/*dialect*/
----------------------------------------------1月--------------------------------------------------------------
SELECT * INTO #A from(
select t4.fname 供应商,T7.FDATAVALUE 银行账号,t5.FALLAMOUNT 实付金额,T7.FENTRYID from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
left join T_BAS_ASSISTANTDATAENTRY_l t7 on t1.F_WNEG_ASSISTANT=t7.FENTRYID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY T8 ON T7.FENTRYID=T8.FENTRYID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' AND T7.FDATAVALUE IS NOT NULL
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{1}-01-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{1}-01-01', 120) + '-01' AS DATE))))
AND T.FPURCHASEORGID<>1
UNION ALL
select t4.fname 供应商,'' 银行账号,t5.FALLAMOUNT 实付金额,t1.F_WNEG_ASSISTANT  from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' 
AND t1.F_WNEG_ASSISTANT=''
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{1}-01-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{1}-01-01', 120) + '-01' AS DATE))))
AND T.FPURCHASEORGID<>1
)t

SELECT 供应商,
CASE WHEN FENTRYID IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 对公1月,
CASE WHEN FENTRYID NOT IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 个账1月
INTO #B
FROM #A

SELECT 供应商,sum(对公1月)对公1月,sum(个账1月)个账1月
INTO #C
FROM #B
group by 供应商





----------------------------------------------2月--------------------------------------------------------------
SELECT * INTO #A2 FROM (
select t4.fname 供应商,T7.FDATAVALUE 银行账号,t5.FALLAMOUNT 实付金额,T7.FENTRYID  from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
left join T_BAS_ASSISTANTDATAENTRY_l t7 on t1.F_WNEG_ASSISTANT=t7.FENTRYID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY T8 ON T7.FENTRYID=T8.FENTRYID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' AND T7.FDATAVALUE IS NOT NULL 
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{1}-02-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{1}-02-01', 120) + '-01' AS DATE))))
AND T.FPURCHASEORGID<>1
UNION ALL 
select t4.fname 供应商,'' 银行账号,t5.FALLAMOUNT 实付金额,t1.F_WNEG_ASSISTANT  from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' 
AND t1.F_WNEG_ASSISTANT=''
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{1}-02-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{1}-02-01', 120) + '-01' AS DATE))))
AND T.FPURCHASEORGID<>1
)T

SELECT 供应商,
CASE WHEN FENTRYID IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 对公2月,
CASE WHEN FENTRYID NOT IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 个账2月
INTO #B2
FROM #A2

SELECT 供应商,sum(对公2月)对公2月,sum(个账2月)个账2月
INTO #C2
FROM #B2
group by 供应商

----------------------------------------------3月--------------------------------------------------------------
SELECT * INTO #A3 FROM (
select t4.fname 供应商,T7.FDATAVALUE 银行账号,t5.FALLAMOUNT 实付金额,T7.FENTRYID from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
left join T_BAS_ASSISTANTDATAENTRY_l t7 on t1.F_WNEG_ASSISTANT=t7.FENTRYID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY T8 ON T7.FENTRYID=T8.FENTRYID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' AND T7.FDATAVALUE IS NOT NULL 
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{1}-03-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{1}-03-01', 120) + '-01' AS DATE))))
AND T.FPURCHASEORGID<>1
UNION ALL
select t4.fname 供应商,'' 银行账号,t5.FALLAMOUNT 实付金额,t1.F_WNEG_ASSISTANT from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' 
AND t1.F_WNEG_ASSISTANT=''
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{1}-03-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{1}-03-01', 120) + '-01' AS DATE))))
AND T.FPURCHASEORGID<>1
)T

SELECT 供应商,
CASE WHEN FENTRYID IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 对公3月,
CASE WHEN FENTRYID NOT IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 个账3月
INTO #B3
FROM #A3

SELECT 供应商,sum(对公3月)对公3月,sum(个账3月)个账3月
INTO #C3
FROM #B3
group by 供应商

----------------------------------------------4月--------------------------------------------------------------
SELECT * INTO #A4 FROM (
select t4.fname 供应商,T7.FDATAVALUE 银行账号,t5.FALLAMOUNT 实付金额,T7.FENTRYID from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
left join T_BAS_ASSISTANTDATAENTRY_l t7 on t1.F_WNEG_ASSISTANT=t7.FENTRYID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY T8 ON T7.FENTRYID=T8.FENTRYID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' AND T7.FDATAVALUE IS NOT NULL 
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{1}-04-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{1}-04-01', 120) + '-01' AS DATE))))
AND T.FPURCHASEORGID<>1
UNION ALL
select t4.fname 供应商,'' 银行账号,t5.FALLAMOUNT 实付金额,t1.F_WNEG_ASSISTANT  from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' 
AND t1.F_WNEG_ASSISTANT=''
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{1}-04-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{1}-04-01', 120) + '-01' AS DATE))))
AND T.FPURCHASEORGID<>1
)T

SELECT 供应商,
CASE WHEN FENTRYID IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 对公4月,
CASE WHEN FENTRYID NOT IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 个账4月
INTO #B4
FROM #A4

SELECT 供应商,sum(对公4月)对公4月,sum(个账4月)个账4月
INTO #C4
FROM #B4
group by 供应商


----------------------------------------------5月--------------------------------------------------------------
SELECT * INTO #A5 FROM(
select t4.fname 供应商,T7.FDATAVALUE 银行账号,t5.FALLAMOUNT 实付金额,T7.FENTRYID from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
left join T_BAS_ASSISTANTDATAENTRY_l t7 on t1.F_WNEG_ASSISTANT=t7.FENTRYID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY T8 ON T7.FENTRYID=T8.FENTRYID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' AND T7.FDATAVALUE IS NOT NULL 
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{1}-05-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{1}-05-01', 120) + '-01' AS DATE))))
AND T.FPURCHASEORGID<>1
UNION ALL
select t4.fname 供应商,'' 银行账号,t5.FALLAMOUNT 实付金额,t1.F_WNEG_ASSISTANT from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' 
AND t1.F_WNEG_ASSISTANT=''
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{1}-05-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{1}-05-01', 120) + '-01' AS DATE))))
AND T.FPURCHASEORGID<>1
)T

SELECT 供应商,
CASE WHEN FENTRYID IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 对公5月,
CASE WHEN FENTRYID NOT IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 个账5月
INTO #B5
FROM #A5
SELECT 供应商,sum(对公5月)对公5月,sum(个账5月)个账5月
INTO #C5
FROM #B5
group by 供应商

----------------------------------------------6月--------------------------------------------------------------
SELECT * INTO #A6 FROM (
select t4.fname 供应商,T7.FDATAVALUE 银行账号,t5.FALLAMOUNT 实付金额,T7.FENTRYID from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
left join T_BAS_ASSISTANTDATAENTRY_l t7 on t1.F_WNEG_ASSISTANT=t7.FENTRYID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY T8 ON T7.FENTRYID=T8.FENTRYID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' AND T7.FDATAVALUE IS NOT NULL 
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{1}-06-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{1}-06-01', 120) + '-01' AS DATE))))
AND T.FPURCHASEORGID<>1
UNION ALL
select t4.fname 供应商,'' 银行账号,t5.FALLAMOUNT 实付金额,t1.F_WNEG_ASSISTANT from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' 
AND t1.F_WNEG_ASSISTANT=''
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{1}-06-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{1}-06-01', 120) + '-01' AS DATE))))
AND T.FPURCHASEORGID<>1
)T

SELECT 供应商,
CASE WHEN FENTRYID IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 对公6月,
CASE WHEN FENTRYID NOT IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 个账6月
INTO #B6
FROM #A6
SELECT 供应商,sum(对公6月)对公6月,sum(个账6月)个账6月
INTO #C6
FROM #B6
group by 供应商

----------------------------------------------7月--------------------------------------------------------------
SELECT * INTO #A7 FROM(
select t4.fname 供应商,T7.FDATAVALUE 银行账号,t5.FALLAMOUNT 实付金额,T7.FENTRYID from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
left join T_BAS_ASSISTANTDATAENTRY_l t7 on t1.F_WNEG_ASSISTANT=t7.FENTRYID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY T8 ON T7.FENTRYID=T8.FENTRYID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' AND T7.FDATAVALUE IS NOT NULL 
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{1}-07-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{1}-07-01', 120) + '-01' AS DATE))))
AND T.FPURCHASEORGID<>1
UNION ALL
select t4.fname 供应商,'' 银行账号,t5.FALLAMOUNT 实付金额,t1.F_WNEG_ASSISTANT from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' 
AND t1.F_WNEG_ASSISTANT=''
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{1}-07-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{1}-07-01', 120) + '-01' AS DATE))))
AND T.FPURCHASEORGID<>1
)T

SELECT 供应商,
CASE WHEN FENTRYID IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 对公7月,
CASE WHEN FENTRYID NOT IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 个账7月
INTO #B7
FROM #A7
SELECT 供应商,sum(对公7月)对公7月,sum(个账7月)个账7月
INTO #C7
FROM #B7
group by 供应商

----------------------------------------------8月--------------------------------------------------------------
SELECT * INTO #A8 FROM (
select t4.fname 供应商,T7.FDATAVALUE 银行账号,t5.FALLAMOUNT 实付金额,T7.FENTRYID from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
left join T_BAS_ASSISTANTDATAENTRY_l t7 on t1.F_WNEG_ASSISTANT=t7.FENTRYID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY T8 ON T7.FENTRYID=T8.FENTRYID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' AND T7.FDATAVALUE IS NOT NULL 
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{1}-08-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{1}-08-01', 120) + '-01' AS DATE))))
AND T.FPURCHASEORGID<>1
UNION ALL
select t4.fname 供应商,'' 银行账号,t5.FALLAMOUNT 实付金额,t1.F_WNEG_ASSISTANT from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' 
AND t1.F_WNEG_ASSISTANT=''
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{1}-08-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{1}-08-01', 120) + '-01' AS DATE))))
AND T.FPURCHASEORGID<>1
)T

SELECT 供应商,
CASE WHEN FENTRYID IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 对公8月,
CASE WHEN FENTRYID NOT IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 个账8月
INTO #B8
FROM #A8
SELECT 供应商,sum(对公8月)对公8月,sum(个账8月)个账8月
INTO #C8
FROM #B8
group by 供应商

----------------------------------------------9月--------------------------------------------------------------
SELECT * INTO #A9 FROM(
select t4.fname 供应商,T7.FDATAVALUE 银行账号,t5.FALLAMOUNT 实付金额,T7.FENTRYID from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
left join T_BAS_ASSISTANTDATAENTRY_l t7 on t1.F_WNEG_ASSISTANT=t7.FENTRYID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY T8 ON T7.FENTRYID=T8.FENTRYID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' AND T7.FDATAVALUE IS NOT NULL 
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{1}-09-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{1}-09-01', 120) + '-01' AS DATE))))
AND T.FPURCHASEORGID<>1
UNION ALL
select t4.fname 供应商,'' 银行账号,t5.FALLAMOUNT 实付金额,t1.F_WNEG_ASSISTANT from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' 
AND t1.F_WNEG_ASSISTANT=''
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{1}-09-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{1}-09-01', 120) + '-01' AS DATE))))
AND T.FPURCHASEORGID<>1
)T

SELECT 供应商,
CASE WHEN FENTRYID IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 对公9月,
CASE WHEN FENTRYID NOT IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 个账9月
INTO #B9
FROM #A9
SELECT 供应商,sum(对公9月)对公9月,sum(个账9月)个账9月
INTO #C9
FROM #B9
group by 供应商

----------------------------------------------10月--------------------------------------------------------------
SELECT * INTO #A10 FROM (
select t4.fname 供应商,T7.FDATAVALUE 银行账号,t5.FALLAMOUNT 实付金额,T7.FENTRYID from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
left join T_BAS_ASSISTANTDATAENTRY_l t7 on t1.F_WNEG_ASSISTANT=t7.FENTRYID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY T8 ON T7.FENTRYID=T8.FENTRYID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' AND T7.FDATAVALUE IS NOT NULL 
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{1}-10-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{1}-10-01', 120) + '-01' AS DATE))))
AND T.FPURCHASEORGID<>1
UNION ALL
select t4.fname 供应商,'' 银行账号,t5.FALLAMOUNT 实付金额,t1.F_WNEG_ASSISTANT from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' 
AND t1.F_WNEG_ASSISTANT=''
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{1}-10-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{1}-10-01', 120) + '-01' AS DATE))))
AND T.FPURCHASEORGID<>1
)T


SELECT 供应商,
CASE WHEN FENTRYID IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 对公10月,
CASE WHEN FENTRYID NOT IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 个账10月
INTO #B10
FROM #A10
SELECT 供应商,sum(对公10月)对公10月,sum(个账10月)个账10月
INTO #C10
FROM #B10
group by 供应商

----------------------------------------------11月--------------------------------------------------------------
SELECT * INTO #A11 FROM(
select t4.fname 供应商,T7.FDATAVALUE 银行账号,t5.FALLAMOUNT 实付金额,T7.FENTRYID from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
left join T_BAS_ASSISTANTDATAENTRY_l t7 on t1.F_WNEG_ASSISTANT=t7.FENTRYID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY T8 ON T7.FENTRYID=T8.FENTRYID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' AND T7.FDATAVALUE IS NOT NULL 
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{1}-11-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{1}-11-01', 120) + '-01' AS DATE))))
AND T.FPURCHASEORGID<>1
UNION ALL
select t4.fname 供应商,'' 银行账号,t5.FALLAMOUNT 实付金额,t1.F_WNEG_ASSISTANT from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' 
AND t1.F_WNEG_ASSISTANT=''
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{1}-11-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{1}-11-01', 120) + '-01' AS DATE))))
AND T.FPURCHASEORGID<>1
)T


SELECT 供应商,
CASE WHEN FENTRYID IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 对公11月,
CASE WHEN FENTRYID NOT IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 个账11月
INTO #B11
FROM #A11
SELECT 供应商,sum(对公11月)对公11月,sum(个账11月)个账11月
INTO #C11
FROM #B11
group by 供应商

----------------------------------------------12月--------------------------------------------------------------
SELECT * INTO #A12 FROM(
select t4.fname 供应商,T7.FDATAVALUE 银行账号,t5.FALLAMOUNT 实付金额,T7.FENTRYID from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
left join T_BAS_ASSISTANTDATAENTRY_l t7 on t1.F_WNEG_ASSISTANT=t7.FENTRYID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY T8 ON T7.FENTRYID=T8.FENTRYID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' AND T7.FDATAVALUE IS NOT NULL 
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{1}-12-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{1}-12-01', 120) + '-01' AS DATE))))
AND T.FPURCHASEORGID<>1
UNION ALL
select t4.fname 供应商,'' 银行账号,t5.FALLAMOUNT 实付金额,t1.F_WNEG_ASSISTANT  from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' 
AND t1.F_WNEG_ASSISTANT=''
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{1}-12-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{1}-12-01', 120) + '-01' AS DATE))))
AND T.FPURCHASEORGID<>1
)T

SELECT 供应商,
CASE WHEN FENTRYID IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 对公12月,
CASE WHEN FENTRYID NOT IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 个账12月
INTO #B12
FROM #A12
SELECT 供应商,sum(对公12月)对公12月,sum(个账12月)个账12月
INTO #C12
FROM #B12
group by 供应商

select * into #D from (
SELECT 供应商,对公1月,个账1月,
0 对公2月,0 个账2月, 
0 对公3月,0 个账3月, 
0 对公4月,0 个账4月,
0 对公5月,0 个账5月,
0 对公6月,0 个账6月,
0 对公7月,0 个账7月,
0 对公8月,0 个账8月,
0 对公9月,0 个账9月,
0 对公10月,0 个账10月,
0 对公11月,0 个账11月,
0 对公12月,0 个账12月
FROM #C
UNION ALL
SELECT 供应商,
0 对公1月, 0 个账1月,
对公2月,个账2月, 
0 对公3月,0 个账3月, 
0 对公4月,0 个账4月,
0 对公5月,0 个账5月,
0 对公6月,0 个账6月,
0 对公7月,0 个账7月,
0 对公8月,0 个账8月,
0 对公9月,0 个账9月,
0 对公10月,0 个账10月,
0 对公11月,0 个账11月,
0 对公12月,0 个账12月
FROM #C2
UNION ALL
SELECT 供应商,
0 对公1月, 0 个账1月,
0 对公2月, 0 个账2月, 
对公3月,个账3月, 
0 对公4月,0 个账4月,
0 对公5月,0 个账5月,
0 对公6月,0 个账6月,
0 对公7月,0 个账7月,
0 对公8月,0 个账8月,
0 对公9月,0 个账9月,
0 对公10月,0 个账10月,
0 对公11月,0 个账11月,
0 对公12月,0 个账12月
FROM #C3
UNION ALL
SELECT 供应商,
0 对公1月, 0 个账1月,
0 对公2月, 0 个账2月, 
0 对公3月, 0 个账3月, 
对公4月,个账4月,
0 对公5月,0 个账5月,
0 对公6月,0 个账6月,
0 对公7月,0 个账7月,
0 对公8月,0 个账8月,
0 对公9月,0 个账9月,
0 对公10月,0 个账10月,
0 对公11月,0 个账11月,
0 对公12月,0 个账12月
FROM #C4
UNION ALL
SELECT 供应商,
0 对公1月, 0 个账1月,
0 对公2月, 0 个账2月, 
0 对公3月, 0 个账3月, 
0 对公4月, 0 个账4月,
对公5月,个账5月,
0 对公6月,0 个账6月,
0 对公7月,0 个账7月,
0 对公8月,0 个账8月,
0 对公9月,0 个账9月,
0 对公10月,0 个账10月,
0 对公11月,0 个账11月,
0 对公12月,0 个账12月
FROM #C5
UNION ALL
SELECT 供应商,
0 对公1月, 0 个账1月,
0 对公2月, 0 个账2月, 
0 对公3月, 0 个账3月, 
0 对公4月, 0 个账4月,
0 对公5月, 0 个账5月,
对公6月,个账6月,
0 对公7月,0 个账7月,
0 对公8月,0 个账8月,
0 对公9月,0 个账9月,
0 对公10月,0 个账10月,
0 对公11月,0 个账11月,
0 对公12月,0 个账12月
FROM #C6
UNION ALL
SELECT 供应商,
0 对公1月, 0 个账1月,
0 对公2月, 0 个账2月, 
0 对公3月, 0 个账3月, 
0 对公4月, 0 个账4月,
0 对公5月, 0 个账5月,
0 对公6月, 0 个账6月,
对公7月,个账7月,
0 对公8月,0 个账8月,
0 对公9月,0 个账9月,
0 对公10月,0 个账10月,
0 对公11月,0 个账11月,
0 对公12月,0 个账12月
FROM #C7
UNION ALL
SELECT 供应商,
0 对公1月, 0 个账1月,
0 对公2月, 0 个账2月, 
0 对公3月, 0 个账3月, 
0 对公4月, 0 个账4月,
0 对公5月, 0 个账5月,
0 对公6月, 0 个账6月,
0 对公7月, 0 个账7月,
对公8月,个账8月,
0 对公9月,0 个账9月,
0 对公10月,0 个账10月,
0 对公11月,0 个账11月,
0 对公12月,0 个账12月
FROM #C8
UNION ALL
SELECT 供应商,
0 对公1月, 0 个账1月,
0 对公2月, 0 个账2月, 
0 对公3月, 0 个账3月, 
0 对公4月, 0 个账4月,
0 对公5月, 0 个账5月,
0 对公6月, 0 个账6月,
0 对公7月, 0 个账7月,
0 对公8月, 0 个账8月,
对公9月,个账9月,
0 对公10月,0 个账10月,
0 对公11月,0 个账11月,
0 对公12月,0 个账12月
FROM #C9
UNION ALL
SELECT 供应商,
0 对公1月, 0 个账1月,
0 对公2月, 0 个账2月, 
0 对公3月, 0 个账3月, 
0 对公4月, 0 个账4月,
0 对公5月, 0 个账5月,
0 对公6月, 0 个账6月,
0 对公7月, 0 个账7月,
0 对公8月, 0 个账8月,
0 对公9月,0 个账9月,
对公10月,个账10月,
0 对公11月,0 个账11月,
0 对公12月,0 个账12月
FROM #C10
UNION ALL
SELECT 供应商,
0 对公1月, 0 个账1月,
0 对公2月, 0 个账2月, 
0 对公3月, 0 个账3月, 
0 对公4月, 0 个账4月,
0 对公5月, 0 个账5月,
0 对公6月, 0 个账6月,
0 对公7月, 0 个账7月,
0 对公8月, 0 个账8月,
0 对公9月,0 个账9月,
0 对公10月,0 个账10月,
对公11月,个账11月,
0 对公12月,0 个账12月
FROM #C11
UNION ALL
SELECT 供应商,
0 对公1月, 0 个账1月,
0 对公2月, 0 个账2月, 
0 对公3月, 0 个账3月, 
0 对公4月, 0 个账4月,
0 对公5月, 0 个账5月,
0 对公6月, 0 个账6月,
0 对公7月, 0 个账7月,
0 对公8月, 0 个账8月,
0 对公9月,0 个账9月,
0 对公10月,0 个账10月,
0 对公11月,0 个账11月,
对公12月,个账12月
FROM #C12
)t

SELECT  供应商,
SUM(对公1月)对公1月,
SUM(个账1月)个账1月,
SUM(对公2月)对公2月,
SUM(个账2月)个账2月,
SUM(对公3月)对公3月,
SUM(个账3月)个账3月,
SUM(对公4月)对公4月,
SUM(个账4月)个账4月,
SUM(对公5月)对公5月,
SUM(个账5月)个账5月,
SUM(对公6月)对公6月,
SUM(个账6月)个账6月,
SUM(对公7月)对公7月,
SUM(个账7月)个账7月,
SUM(对公8月)对公8月,
SUM(个账8月)个账8月,
SUM(对公9月)对公9月,
SUM(个账9月)个账9月,
SUM(对公10月)对公10月,
SUM(个账10月)个账10月,
SUM(对公11月)对公11月,
SUM(个账11月)个账11月,
SUM(对公12月)对公12月,
SUM(个账12月)个账12月,
(SUM(对公1月)+SUM(对公2月)+SUM(对公3月)+SUM(对公4月)+SUM(对公5月)+SUM(对公6月)+SUM(对公7月)+SUM(对公8月)+SUM(对公9月)+SUM(对公10月)+SUM(对公11月)+SUM(对公12月))DGHJ,
(SUM(个账1月)+SUM(个账2月)+SUM(个账3月)+SUM(个账4月)+SUM(个账5月)+SUM(个账6月)+SUM(个账7月)+SUM(个账8月)+SUM(个账9月)+SUM(个账10月)+SUM(个账11月)+SUM(个账12月))SZHJ,
CONVERT(decimal(10,2),(SUM(对公1月)+SUM(对公2月)+SUM(对公3月)+SUM(对公4月)+SUM(对公5月)+SUM(对公6月)+SUM(对公7月)+SUM(对公8月)+SUM(对公9月)+SUM(对公10月)+SUM(对公11月)+SUM(对公12月)+
SUM(个账1月)+SUM(个账2月)+SUM(个账3月)+SUM(个账4月)+SUM(个账5月)+SUM(个账6月)+SUM(个账7月)+SUM(个账8月)+SUM(个账9月)+SUM(个账10月)+SUM(个账11月)+SUM(个账12月)))ZHJ
into #PX
FROM #D
GROUP BY 供应商
--SELECT row_number() over(order by 供应商 ASC) as 序号,*,{0} INTO {1} FROM #PX ORDER BY ZHJ ASC
SELECT row_number() over(order by ZHJ DESC) as 序号,
row_number() over(order by ZHJ DESC) FIDENTITYID,* INTO {0} FROM #PX ORDER BY ZHJ DESC
                                        ", tableName, year);
                                        DBUtils.ExecuteDynamicObject(this.Context, sql);
        }

        protected override string GetIdentityFieldIndexSQL(string tableName) 
        {
            string result = base.GetIdentityFieldIndexSQL(tableName);
            return result;
        }

        //protected override void ExecuteBatch(List listSql) { base.ExecuteBatch(listSql); }
        public override ReportHeader GetReportHeaders(IRptParams filter)
        {
            ReportHeader header = new ReportHeader();
            var ZD00 = header.AddChild("序号", new LocaleValue("序号"));
            ZD00.ColIndex = 0;
            var ZD01 = header.AddChild("供应商", new LocaleValue("供应商"));
            ZD01.ColIndex = 1;
            var CGRQ = header.AddChild("对公1月", new LocaleValue("1月&&对公"), SqlStorageType.SqlDecimal);
            CGRQ.ColIndex = 2;
            var CGY = header.AddChild("个账1月", new LocaleValue("1月&&个账"), SqlStorageType.SqlDecimal);
            CGY.ColIndex = 3;
            var WLGYS = header.AddChild("对公2月", new LocaleValue("2月&&对公"), SqlStorageType.SqlDecimal);
            WLGYS.ColIndex = 4;
            var YFHJ = header.AddChild("个账2月", new LocaleValue("2月&&个账"), SqlStorageType.SqlDecimal);
            YFHJ.ColIndex = 5;
            var XH = header.AddChild("对公3月", new LocaleValue("3月&&对公"), SqlStorageType.SqlDecimal);
            XH.ColIndex = 6;
            var WLBM = header.AddChild("个账3月", new LocaleValue("3月&&个账"), SqlStorageType.SqlDecimal);
            WLBM.ColIndex = 7;
            var GGXH = header.AddChild("对公4月", new LocaleValue("4月&&对公"), SqlStorageType.SqlDecimal);
            GGXH.ColIndex = 8;
            var CPMC = header.AddChild("个账4月", new LocaleValue("4月&&个账"), SqlStorageType.SqlDecimal);
            CPMC.ColIndex = 9;
            var SL = header.AddChild("对公5月", new LocaleValue("5月&&对公"), SqlStorageType.SqlDecimal);
            SL.ColIndex = 10;
            var XS = header.AddChild("个账5月", new LocaleValue("5月&&个账"), SqlStorageType.SqlDecimal);
            XS.ColIndex = 11;
            var DXTJ = header.AddChild("对公6月", new LocaleValue("6月&&对公"), SqlStorageType.SqlDecimal);
            DXTJ.ColIndex = 12;
            var DJ = header.AddChild("个账6月", new LocaleValue("6月&&个账"), SqlStorageType.SqlDecimal);
            DJ.ColIndex = 13;
            var JSDJ = header.AddChild("对公7月", new LocaleValue("7月&&对公"), SqlStorageType.SqlDecimal);
            JSDJ.ColIndex = 14;
            var JSZJE = header.AddChild("个账7月", new LocaleValue("7月&&个账"), SqlStorageType.SqlDecimal);
            JSZJE.ColIndex = 15;
            var FKFS = header.AddChild("对公8月", new LocaleValue("8月&&对公"), SqlStorageType.SqlDecimal);
            FKFS.ColIndex = 16;
            var BZ = header.AddChild("个账8月", new LocaleValue("8月&&个账"), SqlStorageType.SqlDecimal);
            BZ.ColIndex = 17;
            var CKFS = header.AddChild("对公9月", new LocaleValue("9月&&对公"), SqlStorageType.SqlDecimal);
            CKFS.ColIndex = 18;
            var SJGCFHRQ = header.AddChild("个账9月", new LocaleValue("9月&&个账"), SqlStorageType.SqlDecimal);
            SJGCFHRQ.ColIndex = 19;
            var YJDGRQ = header.AddChild("对公10月", new LocaleValue("10月&&对公"), SqlStorageType.SqlDecimal);
            YJDGRQ.ColIndex = 20;
            var XU21 = header.AddChild("个账10月", new LocaleValue("10月&&个账"), SqlStorageType.SqlDecimal);
            XU21.ColIndex = 21;
            var XU22 = header.AddChild("对公11月", new LocaleValue("11月&&对公"), SqlStorageType.SqlDecimal);
            XU22.ColIndex = 22;
            var XU23 = header.AddChild("个账11月", new LocaleValue("11月&&个账"), SqlStorageType.SqlDecimal);
            XU23.ColIndex = 23;
            var XU24 = header.AddChild("对公12月", new LocaleValue("12月&&对公"), SqlStorageType.SqlDecimal);
            XU24.ColIndex = 24;
            var XU25 = header.AddChild("个账12月", new LocaleValue("12月&&个账"), SqlStorageType.SqlDecimal);
            XU25.ColIndex = 25;
            var XU26 = header.AddChild("DGHJ", new LocaleValue("对公合计"), SqlStorageType.SqlDecimal);
            XU26.ColIndex = 26;
            var XU27 = header.AddChild("SZHJ", new LocaleValue("个账合计"), SqlStorageType.SqlDecimal);
            XU27.ColIndex = 27;
            var XU28 = header.AddChild("ZHJ", new LocaleValue("总合计"), SqlStorageType.SqlDecimal);
            XU28.ColIndex = 28;
            return header;
        }

        protected override void ExecuteBatch(List<string> listSql)
        {
            base.ExecuteBatch(listSql);
        }

        //protected override string GetSummaryColumsSQL(List<SummaryField> summaryFields)
        //{
        //    var result = base.GetSummaryColumsSQL(summaryFields);
        //    return result;
        //}

        /// <summary>     
        /// /// 设置报表合计列  
        /// /// </summary>    
        /// /// <param name="filter"></param>   
        /// /// <returns></returns>   
        public override List<SummaryField> GetSummaryColumnInfo(IRptParams filter)
        {
            var result = base.GetSummaryColumnInfo(filter);
            result.Add(new SummaryField("对公1月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("个账1月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("对公2月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("个账2月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("对公3月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("个账3月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("对公4月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("个账4月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("对公5月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("个账5月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("对公6月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("个账6月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("对公7月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("个账7月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("对公8月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("个账8月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("对公9月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("个账9月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("对公10月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("个账10月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("对公11月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("个账11月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("对公12月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("个账12月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("DGHJ", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("SZHJ", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("ZHJ", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            return result;
        }

        protected override System.Data.DataTable GetListData(string sSQL)
        {
            var result = base.GetListData(sSQL);
            return result;
        }

        protected override System.Data.DataTable GetReportData(IRptParams filter)
        {
            var result = base.GetReportData(filter);
            return result;
        }

        protected override System.Data.DataTable GetReportData(string tablename, IRptParams filter)
        {
            var result = base.GetReportData(tablename, filter);
            return result;
        }

        public override int GetRowsCount(IRptParams filter)
        {
            var result = base.GetRowsCount(filter);
            return result;
        }

        protected override string BuilderFromWhereSQL(IRptParams filter)
        {
            string result = base.BuilderFromWhereSQL(filter);
            return result;
        }

        protected override string BuilderSelectFieldSQL(IRptParams filter)
        {
            string result = base.BuilderSelectFieldSQL(filter);
            return result;
        }

        protected override string BuilderTempTableOrderBySQL(IRptParams filter)
        {
            string result = base.BuilderTempTableOrderBySQL(filter);
            return result;
        }

        public override void CloseReport()
        {
            base.CloseReport();
        }

        protected override string CreateGroupSummaryData(IRptParams filter, string tablename)
        {
            string result = base.CreateGroupSummaryData(filter, tablename);
            return result;
        }

        protected override void CreateTempTable(string sSQL)
        {
            base.CreateTempTable(sSQL);
        }

        public override void DropTempTable()
        {
            base.DropTempTable();
        }

        public override System.Data.DataTable GetList(IRptParams filter)
        {
            var result = base.GetList(filter);
            return result;
        }

        public override List<long> GetOrgIdList(IRptParams filter)
        {
            var result = base.GetOrgIdList(filter);
            return result;
        }

        public override List<Kingdee.BOS.Core.Metadata.TreeNode> GetTreeNodes(IRptParams filter)
        {
            var result = base.GetTreeNodes(filter);
            return result;
        }
    }
}
